<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2019/10/23
 * Time: 21:58
 */

namespace app\share\model;


use think\Db;

class PhpExecl
{
    public function __construct()
    {
        vendor("PHPExcel.PHPExcel");
        Vendor('PHPExcel.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.PHPExcel.Writer.Excel2007');
        $this->obj_php_excel=new \PHPExcel();
    }
    /*
     * excel导入
     * @param  $file_name excel文件绝对地址
     * @return  array
     */
    public function readData($file_name)
    {
        $objReader =\PHPExcel_IOFactory::createReader('Excel5');
        $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8
        $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
        array_shift($excel_array);  //删除第一个数组(标题);
        array_shift($excel_array);  //删除示例: 勿删
        return $excel_array;
    }
    //每月定时下载数据
    public function downMore($data,$month)
    {
        $objWriter = \PHPExcel_IOFactory::createWriter($this->obj_php_excel, 'Excel2007');
        //锁定单元格首行
        $objActSheet = $this->obj_php_excel->getActiveSheet()->freezePane('A2');
        //第一行加粗
        $this->obj_php_excel->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);

        $letter =explode(',',"A,B,C,D,E,F,G,H");
        $arrHeader = array('编号','IP地址','网络状态','网络名称',"单位","部门",'查询月份','查询日期');
        //填充表头信息
        $lenth =  count($arrHeader);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]");
        };
        //填充表格信息
        foreach($data as $k=>$v){
            $k +=2;
            $objActSheet->setCellValue('A'.$k,$k-1);
            $objActSheet->setCellValue('B'.$k, $v['ip_address']);
            $objActSheet->setCellValue('C'.$k, $v['ping_status']);
            $objActSheet->setCellValue('D'.$k, $v['net_name']);
            $objActSheet->setCellValue('E'.$k, $v['company_name']);
            $objActSheet->setCellValue('F'.$k, $v['depart_name']);
            $objActSheet->setCellValue('G'.$k, $v['ping_month']);
            $objActSheet->setCellValue('H'.$k, $v['ping_ip_day']);
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(18);
        }
        $width = array(8,15,12,16,25,18,10,12);
        //设置表格的宽度
        $objActSheet->getColumnDimension('A')->setWidth($width[0]);
        $objActSheet->getColumnDimension('B')->setWidth($width[1]);
        $objActSheet->getColumnDimension('C')->setWidth($width[2]);
        $objActSheet->getColumnDimension('D')->setWidth($width[3]);
        $objActSheet->getColumnDimension('E')->setWidth($width[4]);
        $objActSheet->getColumnDimension('F')->setWidth($width[5]);
        $objActSheet->getColumnDimension('G')->setWidth($width[6]);
        $objActSheet->getColumnDimension('H')->setWidth($width[7]);
        $outfile = $month."网络通断记录".".xlsx";
        $file_dir = ROOT_PATH."public".DS."historyexcel".DS;
        $filename = $file_dir.$outfile;
        $objWriter->save($filename);
        $excel_file['more_url_name'] = $outfile;
        $excel_file['create_time'] = time();
        $res_more = Db::name("more_down_month")->where(['more_url_name'=>$excel_file['more_url_name']])->count();
        if($res_more)
        {
            Db::name("more_down_month")
                ->where(['more_url_name'=>$excel_file['more_url_name']])
                ->update(['create_time'=>time()]);
        }else
        {
            Db::name("more_down_month")->insert($excel_file);
        }
        echo "执行成功...  ";
    }
    //excel导出
    public function downData($data)
    {

        $objWriter = \PHPExcel_IOFactory::createWriter($this->obj_php_excel, 'Excel2007');
        //锁定单元格首行
        $objActSheet = $this->obj_php_excel->getActiveSheet()->freezePane('A2');
        //第一行加粗
        $this->obj_php_excel->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);

        $letter =explode(',',"A,B,C,D,E,F,G,H");
        $arrHeader = array('编号', '网络名称','IP地址','查询批次', '时间','连接状态','网络测试规则','重复次数');
        //填充表头信息
        $lenth =  count($arrHeader);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]");
        };
        //填充表格信息
        $status = ['无状态', '畅通', '超时'];
        foreach($data as $k=>$v){
            $k +=2;
            $objActSheet->setCellValue('A'.$k,$k-1);
            $objActSheet->setCellValue('B'.$k, $v['net_name']);
            $objActSheet->setCellValue('C'.$k, $v['ip_address']);
            $objActSheet->setCellValue('D'.$k, date( "mdHis",substr( $v['ping_sort'], 0, 10 )) );
            $objActSheet->setCellValue('E'.$k, date("Y-m-d H:i:s",$v['ping_time']));
            $objActSheet->setCellValue('F'.$k, $status[$v['ping_status']]);
            $objActSheet->setCellValue('G'.$k, '每隔'.$v['interval_time'].'分钟测试');
            $objActSheet->setCellValue('H'.$k, $v['count']);
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(18);
        }
        $width = array(8,15,15,12,20,12,15,10);
        //设置表格的宽度
        $objActSheet->getColumnDimension('A')->setWidth($width[0]);
        $objActSheet->getColumnDimension('B')->setWidth($width[1]);
        $objActSheet->getColumnDimension('C')->setWidth($width[2]);
        $objActSheet->getColumnDimension('D')->setWidth($width[3]);
        $objActSheet->getColumnDimension('E')->setWidth($width[4]);
        $objActSheet->getColumnDimension('F')->setWidth($width[5]);
        $objActSheet->getColumnDimension('G')->setWidth($width[6]);
        $objActSheet->getColumnDimension('H')->setWidth($width[7]);

        $outfile = date("mdHis")."IP网络记录".".xlsx";
        $file_dir = ROOT_PATH."public".DS."historyexcel".DS;
        $filename = $file_dir.$outfile;
        $objWriter->save($filename);
        $excel_file['domain'] = config('excel_domain');
        $excel_file['relative_url'] = "historyexcel".'/'.$outfile;
        returnJson(200,  "下载成功", $excel_file)->send();die;
    }
    //清空一个文件夹
    protected function deldir($path)
    {
        //如果是目录则继续
        if (is_dir($path)) {
            //扫描一个文件夹内的所有文件夹和文件并返回数组
            $p = scandir($path);
            foreach ($p as $val) {
                //排除目录中的.和..
                if ($val != "." && $val != "..") {
                    //如果是目录则递归子目录，继续操作
                    if (is_dir($path . $val)) {
                        //子目录中操作删除文件夹和文件
                        $this->deldir($path . $val . '/');
                        //目录清空后删除空文件夹
                        @rmdir($path . $val . '/');
                    } else {
                        //如果是文件直接删除
                        unlink($path . $val);
                    }
                }
            }
        }
    }

}